﻿using Microsoft.Reporting.WebForms;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WeddingEventManagementApp.Reports
{
    public partial class TransactionReport : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                SetupReportParams();
                SetupReport();
            }
        }

        private void SetupReport()
        {
            ReportViewer1.ProcessingMode = ProcessingMode.Local;
            ReportViewer1.ShowParameterPrompts = true;
            
            LocalReport localReport = ReportViewer1.LocalReport;
            localReport.ReportPath = "Reports/TransactionReport.rdlc";

            ReportParameter paymentParam = new ReportParameter();
            paymentParam.Name = "PaymentType";
            paymentParam.Values.Add(" ");
            paymentParam.Visible = true;

            ReportParameter namesParam = new ReportParameter();
            namesParam.Name = "ClientName";
            namesParam.Values.Add(" ");
            namesParam.Visible = true;
            localReport.SetParameters(new ReportParameter[] { namesParam, paymentParam });

            RefreshReport(localReport);
        }

        private void RefreshReport(LocalReport localReport)
        {
            DateTime startDate = DateTime.Parse(txtStartDate.Text); //new DateTime(2012, 01, 01);
            DateTime endDate = DateTime.Parse(txtEndDate.Text); //new DateTime(2012, 12, 31);

            string clientName;
            if (drpClientName.SelectedItem == null || drpClientName.SelectedItem.Value == "All Clients")
            {
                clientName = null;
            }
            else
            {
                clientName = drpClientName.SelectedItem.Value;
            }

            string paymentType;
            if (drpPaymentType.SelectedItem == null || drpPaymentType.SelectedItem.Value == "All Types")
            {
                paymentType = null;
            }
            else
            {
                paymentType = drpPaymentType.SelectedItem.Value;
            }

            DataTable dt1 = new DataTable("DataSet1");
            GetDataSet1(clientName, startDate, endDate, paymentType, ref dt1);

            ReportDataSource rds1 = new ReportDataSource();
            rds1.Name = "DataSet1";
            rds1.Value = dt1;
            localReport.DataSources.Add(rds1);

            DataTable dt2 = new DataTable("DataSet2");
            GetDataSet2(clientName, startDate, endDate, paymentType, ref dt2);

            ReportDataSource rds2 = new ReportDataSource();
            rds2.Name = "DataSet2";
            rds2.Value = dt2;
            localReport.DataSources.Add(rds2);


            localReport.Refresh();
        }

        protected void btnViewReport_Click(object sender, EventArgs e)
        {
            LocalReport localReport = ReportViewer1.LocalReport;
            localReport.DataSources.Clear();
            RefreshReport(localReport);
        }

        #region params

        private void SetupReportParams()
        {
            //dates
            txtStartDate.Text = GetDefaultDate("start").ToShortDateString();
            txtEndDate.Text = GetDefaultDate("end").ToShortDateString();

            //payment type
            DataTable paymentTypes = new DataTable("PaymentTypes");
            GetPaymentTypes(ref paymentTypes);
            DataRow newPaymentRow = paymentTypes.NewRow();
            newPaymentRow["PaymentType"] = "All Types";
            paymentTypes.Rows.InsertAt(newPaymentRow, 0);

            drpPaymentType.DataSource = paymentTypes;
            drpPaymentType.DataTextField = "PaymentType";
            drpPaymentType.DataValueField = "PaymentType";
            drpPaymentType.DataBind();


            //client name
            DataTable clientNames = new DataTable("ClientNames");
            GetClientNames(ref clientNames);
            DataRow newClientRow = clientNames.NewRow();
            newClientRow["Client Name"] = "All Clients";
            clientNames.Rows.InsertAt(newClientRow, 0);

            drpClientName.DataSource = clientNames;
            drpClientName.DataTextField = "Client Name";
            drpClientName.DataValueField = "Client Name";
            drpClientName.DataBind();
        }

        private void GetPaymentTypes(ref DataTable paymentTypes)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlCreativeSystems"].ConnectionString))
            {
                con.Open();
                SqlDataAdapter adapter = new SqlDataAdapter("select distinct PaymentType from Payment", con);
                adapter.Fill(paymentTypes);
            }
        }

        private void GetClientNames(ref DataTable dataTable)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCreativeSystems"].ConnectionString))
            {
                con.Open();
                SqlDataAdapter adapter = new SqlDataAdapter("sp_getClientNames", con);
                adapter.Fill(dataTable);
            }
        }

        private DateTime GetDefaultDate(string startEnd)
        {
            DateTime currentDate = DateTime.Now;
            if (startEnd == "start")
            {
                return new DateTime(currentDate.Year, 01, 01);
            }
            else if (startEnd == "end")
            {
                return new DateTime(currentDate.Year, 12, 31);
            }
            return DateTime.Now;
        }

        #endregion

        #region datasets

        private void GetDataSet1(string clientName, DateTime startDate, DateTime endDate, string paymentType, ref DataTable dataTable)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlCreativeSystems"].ConnectionString))
            {
                con.Open();

                SqlDataAdapter adp = new SqlDataAdapter("pr_GetTransactions @StartDateRange, @EndDateRange, @ClientName, @PaymentType", con);
                adp.SelectCommand.Parameters.AddWithValue("@StartDateRange", startDate);
                adp.SelectCommand.Parameters.AddWithValue("@EndDateRange", endDate);

                if (clientName == null)
                    adp.SelectCommand.Parameters.AddWithValue("@ClientName", DBNull.Value);
                else
                    adp.SelectCommand.Parameters.AddWithValue("@ClientName", clientName);

                if (paymentType == null)
                    adp.SelectCommand.Parameters.AddWithValue("@PaymentType", DBNull.Value);
                else
                    adp.SelectCommand.Parameters.AddWithValue("@PaymentType", paymentType);

                adp.Fill(dataTable);
            }
        }

        private void GetDataSet2(string clientName, DateTime startDate, DateTime endDate, string paymentType, ref DataTable dataTable)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlCreativeSystems"].ConnectionString))
            {
                con.Open();

                SqlDataAdapter adp = new SqlDataAdapter("pr_GetTransactionSums @StartDateRange, @EndDateRange, @ClientName, @PaymentType", con);
                adp.SelectCommand.Parameters.AddWithValue("@StartDateRange", startDate);
                adp.SelectCommand.Parameters.AddWithValue("@EndDateRange", endDate);

                if (clientName == null)
                    adp.SelectCommand.Parameters.AddWithValue("@ClientName", DBNull.Value);
                else
                    adp.SelectCommand.Parameters.AddWithValue("@ClientName", clientName);

                if (paymentType == null)
                    adp.SelectCommand.Parameters.AddWithValue("@PaymentType", DBNull.Value);
                else
                    adp.SelectCommand.Parameters.AddWithValue("@PaymentType", paymentType);

                adp.Fill(dataTable);
            }
        }

        #endregion

    }
}